Release 10.1A: OpenEdge Data Management:
SQL Reference

Preface

This Preface contains the following sections:

Purpose
Audience
Organization
Typographical conventions
Examples of syntax diagrams (SQL)

Purpose

OpenEdge Data Management: SQL Reference provides specific information on the OpenEdge® SQL language. The reference contains information on SQL statements, functions, reserved words, error messages, data type compatibility, and the language’s compliance with industry standards. The book also provides reference information on the ODBC and JDBC drivers.

Audience

The audience of this book is composed of two groups:

Database administrators will use the book to:
Create and maintain databases.
Create, modify, and revoke user privileges.
Tune database performance.
Perform installation and setup of servers and clients.
Application developers will use the book to:
Manage database connections and set up data sources.
Create database queries.
Tune database queries.
Develop application business logic.

Organization

Part I, SQL Reference

OpenEdge SQL Statements

Describes the purpose and syntax of each OpenEdge SQL statement. A sample is provided for each statement.

OpenEdge SQL Functions

Describes the purpose and syntax of each OpenEdge SQL function. A sample is provided for each function.

OpenEdge SQL Reserved Words

Provides a list of words that have special syntactic meaning to OpenEdge SQL and cannot be used as identifiers for constants, variables, cursors, types, tables, records, subprograms or packages.

OpenEdge SQL Error Messages

Provides a list of error messages generated by the various components of OpenEdge SQL.

OpenEdge SQL System Limits

Provides a list of the maximum sizes for various attributes of the OpenEdge SQL database environment, and for elements of SQL queries addressed to this environment.

OpenEdge SQL System Catalog Tables

Provides a set of system tables for storing information about tables, columns, indexes, constraints, and privileges. This chapter describes those system catalog tables.

Data Type Compatibility

Addresses compatibility issues when using OpenEdge SQL and earlier versions of the Progress database.

OpenEdge SQL Language Elements

Describes Standard SQL language elements that are common to OpenEdge SQL.

Support for the Array Data Type

Details OpenEdge SQL support for 4GL ARRAY data type, including the PRO_ELEMENT, PRO_ARR_ESCAPE, and PRO_ARR_DESCAPE functions.

OpenEdge SQL Elements and Statements in Backus Naur Form

Presents OpenEdge SQL elements and statements in Backus Naur Form.

Compliance with Industry Standards

Addresses compatibility issues when using OpenEdge SQL and earlier versions of its database.

Syntax for 4GL Attributes

Lists and describes SQL keywords to use with statements that allow you to define 4GL attributes for tables and columns.

Part II, JDBC Reference

Java Class Reference

Provides information on OpenEdge SQL Java classes and methods.

JDBC Conformance Notes

Provides information on mapping between JDBC and other data types and return values for database metadata.

Part III, ODBC Reference

OpenEdge SQL and ODBC Data Types

Shows how the OpenEdge data types are mapped to the standard ODBC data types.

SQLGetInfo

Describes return values to SQL GetInfo from the ODBC driver.

ODBC Scalar Functions

Lists scalar functions that ODBC supports and are available to use in SQL statements.

Part IV, ESQL Reference

Embedded SQL

Provides reference information for an ESQL interface.

Typographical conventions

This manual uses the following typographical conventions:

Convention	Description
Bold	Bold typeface indicates commands or characters the user types, provides emphasis, or the names of user interface elements.
Italic	Italic typeface indicates the title of a document, or signifies new terms.
SMALL, BOLD CAPITAL LETTERS	Small, bold capital letters indicate OpenEdge® key functions and generic keyboard keys; for example, GET and CTRL.
KEY1+KEY2	A plus sign between key names indicates a simultaneous key sequence: you press and hold down the first key while pressing the second key. For example, CTRL+X.
KEY1 KEY2	A space between key names indicates a sequential key sequence: you press and release the first key, then press another key. For example, ESCAPE H.
Syntax:
Fixed width	A fixed-width font is used in syntax statements, code examples, system output, and filenames.
Fixed-width italics	Fixed-width italics indicate variables in syntax statements.
Fixed-width bold	Fixed-width bold indicates variables with special emphasis.
UPPERCASE fixed width	Uppercase words are Progress® 4GL language keywords. Although these are always shown in uppercase, you can type them in either uppercase or lowercase in a procedure.
	This icon (three arrows) introduces a multi-step procedure.
	This icon (one arrow) introduces a single-step procedure.
lowercase	Lowercase words are source language elements that are not SQL keywords.
[ ]	Large brackets indicate the items within them are optional.
{ }	Large braces indicate the items within them are required. They are used to simplify complex syntax diagrams.
|	A vertical bar indicates a choice.
...	Ellipses indicate repetition: you can choose one or more of the preceding items.

Examples of syntax diagrams (SQL)

In this example, GRANT, RESOURCE, DBA, and TO are keywords. You must specify RESOURCE, DBA, or both, and at least one user_name. Optionally you can specify additional user_name items; each subsequent user_name must be preceded by a comma:

Syntax

GRANT { RESOURCE, DBA } TO user_name [, user_name ] ... ;

This excerpt from an ODBC application invokes a stored procedure using the ODBC syntax { call procedure_name ( param ) }, where braces and parentheses are part of the language:

Syntax

proc1( param, "{ call proc2 (param) }", param);

In this example, you must specify a table_name, view_name, or synonym, but you can choose only one. In all SQL syntax, if you specify the optional owner_name qualifier, there must not be a space between the period separator and table_name, view_name, or synonym:

Syntax

CREATE [ PUBLIC ] SYNONYM synonym   FOR [ owner_name.]{table_name |view_name |synonym } ;

In this example, you must specify table_name or view_name:

Syntax

DELETE FROM [ owner_name.]{table_name |view_name }   [ WHERE search_condition ] ;

In this example, you must include one expression (expr) or column position (posn), and optionally you can specify the sort order as ascending (ASC) or descending (DESC). You can specify additional expressions or column positions for sorting within a sorted result set. The SQL engine orders the rows on the basis of the first expr or posn. If the values are the same, the second expr or posn is used in the ordering:

Syntax

ORDER BY { expr | posn } [ ASC | DESC ]   [ , [ { expr | posn } [ ASC | DESC ] ] ... ]

Long syntax descriptions split across lines

Some syntax descriptions are too long to fit on one line. When syntax descriptions are split across multiple lines, groups of optional and groups of required items are kept together in the required order.

In this example, CREATE VIEW is followed by several optional items:

Syntax

CREATE VIEW [ owner_name.]view_name   [ ( column_name [, column_name ] ... ) ]   AS [ ( ] query_expression [ ) ] [ WITH CHECK OPTION ] ;